package com.baosight.xCyber.util;

import com.baosight.xCyber.model.TagNode;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;

/**
 * 导入解析excel
 * @author wuqihang
 */
public class ExcelUtil {

    private static Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
    private static Map<String, TagNode> dataMap;
    private static Map<String, TagNode> rootMap;

    public static Map<String, TagNode> mainMap;
    public static Map<String, TagNode> treeMap;


    public static Map<String, TagNode> getDataByExcel(InputStream in, String fileName, Map<String,Object> propertyMap){
        mainMap = new TreeMap<>();
        treeMap = new HashMap<>();
        dataMap = new TreeMap<>();
        rootMap = new TreeMap<>();

        //获取excel配置信息
        int tagIndex = (int)propertyMap.get("tagIndex");
        int zoneIndex = (int)propertyMap.get("zoneIndex");
        boolean isZoneMore = (boolean)propertyMap.get("isZoneMore");

        String treeName = "tree";
        if(fileName.contains("_")){
            treeName = fileName.substring(fileName.lastIndexOf("_")+1,
                    fileName.lastIndexOf("."));
        }

        TagNode tagNode = new TagNode(treeName);
        dataMap.put("root",tagNode);
        mainMap.put("root",tagNode);
        //检验excel文件
        try {
            Workbook work = getWorkbook(in, fileName);
            if (null == work) {
                throw new IOException("未检测到"+fileName+"文件");
            }
            logger.info(treeName+":IsZoneMore="+isZoneMore);

            Row row = null;
            Sheet sheet = null;
            for (int i=0;i<work.getNumberOfSheets();i++) {
                sheet = work.getSheetAt(i);
                if(sheet == null
                        ||sheet.getSheetName().trim().equals("")
                        ||sheet.getRow(0)==null) {
                    continue;
                }

                logger.info(treeName+":识别"+sheet.getSheetName()+"页的数据");
                String sheetName= sheet.getSheetName();
                if("title".equals(sheetName)){
                    //解析sheet(root根节点)层
                    for(int m=sheet.getFirstRowNum(); m<=sheet.getLastRowNum(); m++){
                        row = sheet.getRow(m);
                        if(row==null
                                ||row.getCell(0)==null
                                ||!row.getCell(0).getStringCellValue().contains("title")){
                            continue;
                        }
                        TagNode titleNode = new TagNode(treeName);
                        titleNode.tagName = row.getCell(row.getFirstCellNum()).getStringCellValue();
                        titleNode.showName = row.getCell(row.getFirstCellNum()+1).getStringCellValue();
                        initRootMap(titleNode,sheetName);
                    }
                }else{
                    //解析sheet层
                    TagNode sheetNode= new TagNode(treeName);
                    sheetNode.tagName=sheet.getSheetName();
                    sheetNode.parentId = "root";
                    sheetNode.showName=sheet.getSheetName();
                    initRootMap(sheetNode,sheetName);

                    //解析数据层
                    int retVal=1;
                    for(int x=sheet.getFirstRowNum(); x<=sheet.getLastRowNum();x++){
                        row = sheet.getRow(x);

                        //计算区域列合并单元格的数目
                        if(row!=null && row.getCell(0) != null && x==sheet.getFirstRowNum()){
                            retVal = getMergerCellRegionRow(sheet,x,zoneIndex)+1;
                            continue;
                        }
                        //过滤非法的数据行（第一行固定表头不解析）
                        if(row==null
                                || row.getCell(0) == null
                                || row.getCell(tagIndex)==null
                                || row.getCell(zoneIndex)==null){
                            continue;
                        }
                        //解析zone区域结构
                        TagNode zoneNode;
                        if(isZoneMore){
                            for(int a=zoneIndex+retVal-1;a>=zoneIndex;a--){
                                String tagName = row.getCell(a).getStringCellValue();
                                if(!mainMap.containsKey(tagName)&&!tagName.equals("")){
                                    zoneNode = new TagNode(treeName);
                                    zoneNode.tagName = tagName;
                                    zoneNode.showName = tagName;
                                    String parentName = row.getCell(a+1).getStringCellValue();
                                    if(!mainMap.containsKey(parentName)){
                                        parentName = sheetName;
                                    }
                                    zoneNode.parentId = parentName;
                                    initRootMap(zoneNode,sheetName);
                                }
                            }
                        }else{
                            String tagName = row.getCell(zoneIndex).getStringCellValue();
                            if(!mainMap.containsKey(tagName)&&!tagName.equals("")){
                                zoneNode=new TagNode(treeName);
                                zoneNode.tagName = tagName;
                                zoneNode.showName = tagName;
                                zoneNode.parentId = sheetName;
                                initRootMap(zoneNode,sheetName);
                            }
                        }
                        //解析数据节点层
                        TagNode dataNode = new TagNode(treeName);
                        String temp = row.getCell(tagIndex).getStringCellValue();
                        if(temp.contains(":")){
                            dataNode.tagName =temp.split(":")[0];
                            dataNode.desc = temp.split(":")[1];
                        }else{
                            dataNode.tagName = temp;
                        }
                        dataNode.showName = dataNode.tagName;
                        dataNode.parentId = row.getCell(zoneIndex).getStringCellValue();
                        dataNode.source = "excel";
                        initTreeMap(dataNode);
                    }
                }
            }
            if(rootMap.size()!=0){
                rootMap=((TreeMap<String,TagNode>) rootMap).descendingMap();
                return GetRoot.initRoot(rootMap,dataMap,mainMap);
            }else if(dataMap.get("root").getItems().size()==1){
                //仅限于excel中只存在一个sheet页
                mainMap.remove("root");
                TagNode rootNode = dataMap.get("root").getItems().get(0);
                rootNode.setParentId("");
                dataMap.put("root",rootNode);
            }else{
                mainMap.put("root",tagNode);
            }
            logger.info(treeName+":建立树完成");

            work.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return dataMap;
    }


    /**
     * 判断文件格式
     * @param in
     * @param fileName
     * @return
     */
    private static Workbook getWorkbook(InputStream in, String fileName) {
        Workbook book = null;
        String fileType = fileName.substring(fileName.lastIndexOf("."));

        try {
            if(".xls".equalsIgnoreCase(fileType)) {
                book = new HSSFWorkbook(in);
            } else if (".xlsx".equalsIgnoreCase(fileType)) {
                book = new XSSFWorkbook(in);
            } else {
                throw new IOException("请上传excel文件！");
            }

        } catch (IOException e) {
            logger.error("文件格式错误："+e.getMessage());
        }
        return book;
    }

    private static void initRootMap(TagNode tagNode, String sheetName){
        String tagName = tagNode.tagName;
        if(sheetName.trim().equalsIgnoreCase("title")){
            if(tagName.toLowerCase().contains("title")){
                //储存结构中自定义的根节点
                rootMap.put(tagName.trim(),tagNode);
            }else{
                return;
            }
        } else{
            if(tagName.equals("")){
                return;
            }
            TagNode parentNode = mainMap.get(tagNode.parentId);
            List<TagNode> childs = parentNode.getItems();
            if (!childs.contains(tagNode)) {
                childs.add(tagNode);
                parentNode.setItemFlag(true);
                parentNode.setItems(childs);
            }
        }
        //储存结构节点
        mainMap.put(tagName,tagNode);
    }

    private static void initTreeMap(TagNode tagNode){
        String tagName = tagNode.tagName;
        if(tagName.equals("")){
            return;
        }

        if(!tagNode.parentId.equals("")){
            tagNode.setItems(null);
            TagNode parentNode = mainMap.get(tagNode.parentId);
            if(parentNode == null){
                return;
            }
            parentNode.setItemFlag(false);
            parentNode.getItems().add(tagNode);
            treeMap.put(tagName,tagNode);
        }
    }

    private static int getMergerCellRegionRow(Sheet sheet, int cellRow, int cellCol) {
        int retVal = 0;
        int sheetMergerCount = sheet.getNumMergedRegions();
        for (int i = 0; i < sheetMergerCount; i++) {
            CellRangeAddress cra = sheet.getMergedRegion(i);
            //合并单元格CELL起始行
            int firstRow = cra.getFirstRow();

            //合并单元格CELL结束行
            int lastRow = cra.getLastRow();

            //判断该单元格是否是在合并单元格中
            if (cellRow >= firstRow && cellRow <= lastRow) {
                retVal = lastRow - firstRow + 1; // 得到合并的行数
                break;
            }
        }
        return retVal;
    }

    private static int getIndexByName(String name,Row row){
        for(int i = row.getFirstCellNum();i<row.getLastCellNum();i++){
            String value = row.getCell(i).getStringCellValue();
            if (value.equals(name)) {
                return i;
            }
        }
        return 6;
    }


}
